package com.campus.dao.impl;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.campus.dao.StudentDao;
import com.campus.model.Student;
import com.alibaba.excel.read.listener.ReadListener;
import com.campus.model.StudentExcelData;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.io.File;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

@Repository
@Slf4j
public class StudentDaoImpl implements StudentDao {
    @Autowired
    JdbcTemplate jdbcTemplate;

    private static final RowMapper<Student> rowMapper = new RowMapper<Student>() {
        @Override
        public Student mapRow(ResultSet rs, int rowNum) throws SQLException {
            Student student = new Student();
            student.setId(rs.getInt("id"));
            student.setStuName(rs.getString("stu_name"));
            student.setFromPlace(rs.getString("from_place"));
            student.setParentPhone(rs.getString("parent_phone"));
            student.setCurrentGrade(rs.getString("current_grade"));
            student.setIsFirst(rs.getString("is_first"));
            student.setLoginId(rs.getInt("login_id"));
            student.setComment(rs.getString("comment"));
            return student;
        }
    };

    @Override
    public int insertStudent(Student student) {
        String sql = "insert into t_manager_student (stu_name,from_place,parent_phone,current_grade,is_first,comment,login_id) " +
                "values(?,?,?,?,?,?,?)";
        return jdbcTemplate.update(sql,
                student.getStuName(),
                student.getFromPlace(),
                student.getParentPhone(),
                student.getCurrentGrade(),
                student.getIsFirst(),
                student.getLoginId(),
                student.getComment());
    }

    @Override
    public List<Student> importStudentsFromExcel(File excelFile) {
        List<Student> students = new ArrayList<>();
        EasyExcel.read(excelFile, StudentExcelData.class, new ReadListener<StudentExcelData>() {
            @Override
            public void invoke(StudentExcelData data, AnalysisContext context) {
                Student student = new Student();
                student.setStuName(data.getStuName());
                student.setFromPlace(data.getFromPlace());
                student.setParentPhone(data.getParentPhone());
                student.setCurrentGrade(data.getCurrentGrade());
                student.setIsFirst(data.getIsFirst());
                student.setLoginId(data.getLoginId());
                student.setComment(data.getComment());
                students.add(student);
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {
                // All data has been processed
            }
        }).sheet().doRead();
        return students;
    }

    @Override
    public int batchImportStudents(List<Student> students) {
        String sql = "INSERT INTO t_manager_student (stu_name, from_place, parent_phone, current_grade, is_first, login_id, comment)" +
                " VALUES (?, ?, ?, ?, ?, ?, ?)";

        // 创建一个保存所有参数的 List
        List<Object[]> batchArgs = new ArrayList<>();
        for (Student student : students) {
            batchArgs.add(new Object[] {
                    student.getStuName(),
                    student.getFromPlace(),
                    student.getParentPhone(),
                    student.getCurrentGrade(),
                    student.getIsFirst(),
                    student.getLoginId(),
                    student.getComment()
            });
        }
        int[] updateCounts = jdbcTemplate.batchUpdate(sql, batchArgs);
        int rowsAffected = Arrays.stream(updateCounts).sum();
        return rowsAffected;
    }

    @Override
    public int approveStudent(Integer id) {
        String sql = "UPDATE t_manager_student SET comment = 'Approved' WHERE id = ?";
        return jdbcTemplate.update(sql, id);
    }

    @Override
    public int batchApproveStudents(List<Integer> studentIds) {
        String sql = "UPDATE t_manager_student SET comment = 'Approved' WHERE id = ?";
        int result = 0;
        for (Integer id : studentIds) {
            result += jdbcTemplate.update(sql, id);
        }
        return result;
    }

    @Override
    public Student findById(Integer id) {
        String sql = "SELECT * FROM t_manager_student WHERE id = ?";
        return jdbcTemplate.queryForObject(sql, new Object[]{id}, rowMapper);
    }
}
